<?php

function get_orgId_by_sponsor($sponsor_uid) {
    $query = "SELECT * FROM {content_type_organization} WHERE field_org_sponsor_uid = %d";
    $list = array();
    //$result = db_fetch_object(db_query($query, $sponsor_uid));
    $result = db_query($query, $sponsor_uid);
    while ($s = db_fetch_object($result)) {
        $list[] = $s->nid;
    }
    return $list;
    //return $result->nid;
}

function get_orgId_by_seller($seller_uid) {
    $content = content_profile_load('profile', $seller_uid);
    return $content->field_profile_org;
}

function get_org_nid_list_by_agent($agent_uid) {
    $query = "SELECT * FROM {content_type_organization} WHERE field_org_agent_uid = %d";
    $list = array();
    $result = db_query($query, $agent_uid);
    while ($s = db_fetch_object($result)) {
        $list[] = $s->nid;
    }
    return $list;
}

function get_org_nid_by_campaign_id($cid) {
    $query = "SELECT field_campaign_org_nid FROM {content_type_organization} o
              JOIN content_type_sale_campaign sc ON o.nid = sc.field_campaign_org_nid 
              WHERE sc.nid = %d";
    $result = db_query($query, $cid);
    $s = db_fetch_object($result);
    return $s->field_campaign_org_nid;
}

function get_org_list_by_agent($agent_uid) {
    $query = "SELECT * FROM {content_type_organization} WHERE field_org_agent_uid = %d";
    $list = array();
    $result = db_query($query, $agent_uid);
    while ($s = db_fetch_object($result)) {
        $list[] = $s;
    }
    return $list;
}

function get_sales_campaign_list_by_org($org_nid) {
    if (is_array($org_nid)) {
        for ($i = 1; $i < count($org_nid); $i++) {
            $extraClause .= "OR field_campaign_org_nid = %d ";
        }
    }
    $query = "SELECT * FROM {content_type_sale_campaign} WHERE field_campaign_org_nid = %d " . $extraClause;
    $list = array();

    $result = db_query($query, $org_nid);
    while ($s = db_fetch_object($result)) {
        $list[] = $s;
    }
    return $list;
}

function get_sales_campaign_nid_list_by_org($org_nid) {
    $query = "SELECT * FROM {content_type_sale_campaign} WHERE field_campaign_org_nid = %d";
    $list = array();
    $result = db_query($query, $org_nid);
    while ($s = db_fetch_object($result)) {
        $list[] = $s->nid;
    }
    return $list;
}
/*
 * Get future sales campaing node id list by organization node id
 * 
 * @param $org_nid
 * 
 * @return
 *        campaign node id list
 */
function get_future_sales_campaign_nid_list_by_org($org_nid) {
    $query = "SELECT * FROM {content_type_sale_campaign} WHERE field_campaign_org_nid = %d";
    $list = array();
    $result = db_query($query, $org_nid);
    while ($s = db_fetch_object($result)) {
        $from = explode("T", $s->field_duration_value);
        $from_date = $from[0];
        $to = explode("T", $s->field_duration_value2);
        $to_date = $to[0];
        $from_year = ((int) substr($from_date, 0, 4));
        $from_month = ((int) substr($from_date, 5, 2));
        $from_day = ((int) substr($from_date, 8, 2));
        $from_timestamp = mktime(0, 0, 0, $from_month, $from_day, $from_year);

        $to_year = ((int) substr($to_date, 0, 4));
        $to_month = ((int) substr($to_date, 5, 2));
        $to_day = ((int) substr($to_date, 8, 2));
        $to_timestamp = mktime(0, 0, 0, $to_month, $to_day, $to_year);
        if (time() <= $from_timestamp) {
            $list[] = $s->nid;
        }
        
    }
    return $list;
}

/*
 * get campaign nid by seller uid, only get current valid campaign
 * 
 * @param $seller_uid
 *      seller's user id
 * 
 * @return
 *      campaign's nid
 */

function get_sales_compaign_nid_by_seller($seller_uid) {
//    $campaigns = db_query("SELECT * FROM {content_type_sale_campaign} WHERE content_type_sale_campaign.field_campaign_org_nid IN 
//        (SELECT content_type_profile.field_profile_org_nid FROM {content_type_profile} WHERE content_type_profile.nid IN 
//        (SELECT node.nid FROM {node} WHERE node.uid = %d))", $seller_uid);
    $sql = "
        select c.nid, cn.title from {content_type_sale_campaign} c
        JOIN {content_field_profile_org} org ON c.field_campaign_org_nid = org.field_profile_org_nid 
        JOIN {node} pn ON pn.nid = org.nid 
        JOIN {node} cn ON cn.nid = c.nid
        WHERE pn.uid = %d
    ";

    $campaigns = db_query($sql, $seller_uid);


    //$campaign_nid;
    $campaign_list = array();
    while ($c = db_fetch_object($campaigns)) {
        $from = explode("T", $c->field_duration_value);
        $from_date = $from[0];
        $to = explode("T", $c->field_duration_value2);
        $to_date = $to[0];
        $from_year = ((int) substr($from_date, 0, 4));
        $from_month = ((int) substr($from_date, 5, 2));
        $from_day = ((int) substr($from_date, 8, 2));
        $from_timestamp = mktime(0, 0, 0, $from_month, $from_day, $from_year);

        $to_year = ((int) substr($to_date, 0, 4));
        $to_month = ((int) substr($to_date, 5, 2));
        $to_day = ((int) substr($to_date, 8, 2));
        $to_timestamp = mktime(0, 0, 0, $to_month, $to_day, $to_year);
        if (time() >= $from_timestamp && time() <= $to_timestamp) {
            $campaign_nid = $c->nid;
            $campaign_list[$c->nid] = $c->title;
        }

        //$campaign_list[$c->nid] = $c->title;
    }
    return $campaign_list;
}

/*
 * get on-going campaign by seller uid
 */

function get_on_going_compaign_nid_by_seller($seller_uid) {
//    $campaigns = db_query("SELECT * FROM {content_type_sale_campaign} WHERE content_type_sale_campaign.field_campaign_org_nid IN 
//        (SELECT content_type_profile.field_profile_org_nid FROM {content_type_profile} WHERE content_type_profile.nid IN 
//        (SELECT node.nid FROM {node} WHERE node.uid = %d))", $seller_uid);
    $sql = "
        select c.nid, cn.title, c.field_duration_value, c.field_duration_value2 from {content_type_sale_campaign} c
        JOIN {content_field_profile_org} org ON c.field_campaign_org_nid = org.field_profile_org_nid 
        JOIN {node} pn ON pn.nid = org.nid 
        JOIN {node} cn ON cn.nid = c.nid
        WHERE pn.uid = %d
    ";

    $campaigns = db_query($sql, $seller_uid);
    $campaign_nids = array();
    $campaign_nid = -1;
    //$campaign_list = array();
    while ($c = db_fetch_object($campaigns)) {
        $from = explode("T", $c->field_duration_value);
        $from_date = $from[0];
        $to = explode("T", $c->field_duration_value2);
        $to_date = $to[0];
        $from_year = ((int) substr($from_date, 0, 4));
        $from_month = ((int) substr($from_date, 5, 2));
        $from_day = ((int) substr($from_date, 8, 2));
        $from_timestamp = mktime(0, 0, 0, $from_month, $from_day, $from_year);

        $to_year = ((int) substr($to_date, 0, 4));
        $to_month = ((int) substr($to_date, 5, 2));
        $to_day = ((int) substr($to_date, 8, 2));
        $to_timestamp = mktime(0, 0, 0, $to_month, $to_day, $to_year);
        if (time() >= $from_timestamp && time() <= $to_timestamp) {
            $campaign_nids[] = $c->nid;
            $campaign_nid = $c->nid;
        }
    }
    return $campaign_nids;
}

/*
 * get on-going campaign by organization id
 */

function get_on_going_compaign_nid_by_org_id($org_id) {
    $sql = "
        select c.nid as campid, field_duration_value, field_duration_value2 from content_type_sale_campaign c
        JOIN content_type_organization org ON c.field_campaign_org_nid = org.nid 
        WHERE org.nid = %d
    ";

    $campaigns = db_query($sql, $org_id);


    $campaign_nid = -1;
    //$campaign_list = array();
    while ($c = db_fetch_object($campaigns)) {
        $from = explode("T", $c->field_duration_value);
        $from_date = $from[0];
        $to = explode("T", $c->field_duration_value2);
        $to_date = $to[0];
        $from_year = ((int) substr($from_date, 0, 4));
        $from_month = ((int) substr($from_date, 5, 2));
        $from_day = ((int) substr($from_date, 8, 2));
        $from_timestamp = mktime(0, 0, 0, $from_month, $from_day, $from_year);

        $to_year = ((int) substr($to_date, 0, 4));
        $to_month = ((int) substr($to_date, 5, 2));
        $to_day = ((int) substr($to_date, 8, 2));
        $to_timestamp = mktime(0, 0, 0, $to_month, $to_day, $to_year);
        if (time() >= $from_timestamp && time() <= $to_timestamp) {
            $campaign_nid = $c->campid;
        }
    }
    return $campaign_nid;
}

/*
 * get list of campaign nids by sponsor uid
 * 
 * @param $seller_uid
 *      seller user id
 * 
 * @return
 *      An array of node ids mapping to the campaign title
 */

function get_sales_compaign_nid_by_sponsor($seller_uid) {
    if ($seller_uid == 1) {
        $result = db_query("SELECT node.nid, node.title FROM {node} WHERE node.nid IN
            (SELECT content_type_sale_campaign.nid FROM {content_type_sale_campaign})");
    } else {
        //sponsor uid -> campaign
        $result = db_query("SELECT node.nid, node.title FROM {node} WHERE node.nid IN 
            (SELECT content_type_sale_campaign.nid FROM {content_type_sale_campaign} WHERE field_campaign_org_nid IN 
            (SELECT content_type_organization.nid FROM {content_type_organization} WHERE content_type_organization.field_org_sponsor_uid = %d))", $seller_uid);
    }
    $object = array();
    while ($o = db_fetch_object($result)) {
        $object[$o->nid] = $o->title;
    }
    return $object;
}

/*
 * get list of campaign nids by team id
 * 
 * @param $tid
 *      team nid
 * 
 * @return
 *      A node id
 */

function _get_campaign_id_by_team_id($tid) {
    $sql = "SELECT field_team_campaign_nid
            FROM {content_type_team} team 
            WHERE team.nid = %d";

    $result = db_query($sql, $tid);
    $s = db_fetch_object($result);
    return $s;
}

function get_agent_uid_by_org_nid($nid) {
    $sql = 'SELECT field_org_agent_uid FROM {content_type_organization} where nid = %d';
    $result = db_query($sql, array($nid));

    return db_fetch_object($result)->field_org_agent_uid;
}
function get_sponsor_uid_by_org_nid($nid){
    $sql = 'SELECT field_org_sponsor_uid FROM {content_type_organization} where nid = %d';
    $result = db_query($sql, array($nid));
    return db_fetch_object($result)->field_org_sponsor_uid;
}

/**
 * find all the sellers of one certain sponsor.
 * @param type $sponsor_uid
 * @return sellers array. 
 */
function get_sellers_by_sponsor($sponsor_uid) {
    $org_nid = get_orgId_by_sponsor($sponsor_uid);
    if (is_array($org_nid)) {
        for ($i = 1; $i < count($org_nid); $i++) {
            $extraClause .= "OR cfpo.field_profile_org_nid = %d ";
        }
    }

    $query = "
        SELECT u.uid as uid, u.name as name, cfpo.field_profile_org_nid as org_nid, ctp.field_firstname_value, ctp.field_lastname_value, ctp.field_student_grade_value 
        FROM {content_type_profile} ctp
        JOIN {content_field_profile_org} cfpo on ctp.nid = cfpo.nid
        JOIN {node} n on ctp.nid=n.nid 
        JOIN {users} u ON n.uid = u.uid  
        JOIN users_roles ur ON ur.uid = u.uid
        WHERE ur.rid = 5 AND (cfpo.field_profile_org_nid = %d 
        " . $extraClause . ") ";
    $list = array();
    $result = db_query($query, $org_nid);
    while ($s = db_fetch_object($result)) {
        $list[] = $s;
    }
    return $list;
}

function get_individual_goal_by_team($cid, $tid) {
    $sql = "select campaign.field_compaign_goal_value, count(*) as num, team.field_team_goal_value from {content_type_sale_campaign} campaign
                JOIN {content_type_team} team ON campaign.nid = team.field_team_campaign_nid
                JOIN {content_field_team_members} members ON members.nid = team.nid 
                WHERE campaign.nid = %d  and team.nid = %d;
               ";
    $result = db_query($sql, array($cid, $tid));
    $campaign = db_fetch_object($result);
    $goal = preg_replace("/[^0-9 ]/", '', $campaign->field_team_goal_value);
    return round($goal / $campaign->num, 2);
}

function get_individual_goal_by_campaign($cid) {
    $sql = "select campaign.field_compaign_goal_value, count(porg.nid) as num from {content_type_sale_campaign} campaign
                JOIN {content_type_organization} org ON campaign.field_campaign_org_nid = org.nid
                JOIN {content_field_profile_org} porg ON porg.field_profile_org_nid = org.nid
                WHERE campaign.nid = %d
               ";
    $result = db_query($sql, $cid);
    $campaign = db_fetch_object($result);

    return round($campaign->field_compaign_goal_value / $campaign->num, 2);
}

function get_all_org_by_seller($seller_uid) {
    $sql = "SELECT org.nid, org.title, user_node.uid FROM {content_field_profile_org} cpo 
                JOIN {node} user_node ON cpo.nid = user_node.nid 
                JOIN node org ON org.nid = cpo.field_profile_org_nid WHERE user_node.uid = %d";
    $result = db_query($sql, $seller_uid);

    $list = array();
    while ($s = db_fetch_object($result)) {
        $list[] = $s;
    }

    return $list;
}

function get_all_orgs_active_camp() {
    $sql = "SELECT * FROM node n
                JOIN content_type_organization cto ON n.nid = cto.nid
                WHERE type = 'organization'";
    $result = db_query($sql);
    $list = array();
    while ($s = db_fetch_object($result)) {
        $s->on_going_camp = get_on_going_compaign_nid_by_org_id($s->nid);
        if($s->on_going_camp != -1)
        $list[] = $s;
    }
    return $list;
}

/* function get_campaign_by_campaign_id($cid){
  $sql = "SELECT * FROM  content_type_sale_campaign sc JOIN node n ON n.nid = sc.nid WHERE sc.nid = %d";
  $result = db_query($sql, $cid);
  $s = db_fetch_object($result);
  return $s;
  } */
function get_active_campaign_by_campaign_list($camp_list){
    $is_on_going = false;
    $campaign_title;
    $start_time;
    $end_time;
    $camp_goal;
    $campid;
    $campaign;
    
    foreach($camp_list as $c) {
    $from = explode("T", $c->field_duration_value);
    $from_date = $from[0];
    $to = explode("T", $c->field_duration_value2);
    $to_date = $to[0];
    $from_year = ((int) substr($from_date, 0, 4));
    $from_month = ((int) substr($from_date, 5, 2));
    $from_day = ((int) substr($from_date, 8, 2));
    $from_timestamp = mktime(0, 0, 0, $from_month, $from_day, $from_year);

    $to_year = ((int) substr($to_date, 0, 4));
    $to_month = ((int) substr($to_date, 5, 2));
    $to_day = ((int) substr($to_date, 8, 2));
    $to_timestamp = mktime(0, 0, 0, $to_month, $to_day, $to_year);
    if (time() >= $from_timestamp && time() <= $to_timestamp) {
        $campaign = $c;
        $is_on_going = true;
        $campaign_title = $c->title;
        $start_time = $from_date;
        $end_time = $to_date;
        $camp_goal = $c->field_compaign_goal_value;
        $campid = $c->nid;
        return $campaign;
    } else {
        //push the past due campain into $campaign list
        //array_push($campaign_list, $c);
    }
    if ($start_time == NULL) {
        $begin_time = '';
    } else {
        $begin_time = date("M d Y", strtotime("$start_time"));
    }

    if ($end_time == NULL) {
        $finish_time = '';
    } else {
        $finish_time = date("M d Y", strtotime("$end_time"));
    }
    
}
return $campaign;
}
?>
